package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.queryParams.ReservoirQueryParams;
import com.css.fxfzypg.modules.baseData.repository.ReservoirRepository;
import com.css.fxfzypg.modules.baseData.entity.ReservoirEntity;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @create 2022-08-15 15:25
 */
@Repository
public class ReservoirRepositoryImpl implements ReservoirRepository {


    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public ReservoirEntity saveReservoir(ReservoirEntity reservoirEntity) {
  /*      String sql = "";
        sql = "insert into data_reservoir (res_longitude,res_latitude,res_name,res_scale,res_capacity,res_result_type,res_time,isdelete,create_time) values(?,?,?,?,?,?,?,?,?) ";
        jdbcTemplate.update(sql,reservoirEntity.getResLongitude(),
                reservoirEntity.getResLatitude()
        ,reservoirEntity.getResName(),
                reservoirEntity.getResScale(),
                reservoirEntity.getResCapacity(),
                reservoirEntity.getResResultType(),
                reservoirEntity.getResTime(),
                reservoirEntity.getIsdelete(),
                reservoirEntity.getCreateTime());
        return reservoirEntity;*/
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                String sql = "";
                sql = "insert into data_reservoir (res_longitude,res_latitude,res_name,res_scale,res_capacity,res_result_type,res_time,isdelete,create_time) values(?,?,?,?,?,?,?,?,?) ";
                //  sql="insert into data_school values(null,?,?,?,?,?,?,?,null ,?)";

                //String sql_sms = "insert into  sms(title,content,date_s,form,sffs,by1,by2,by3) values (?,?,'"+dates+"',?,?,?,?,?)";
                PreparedStatement ps = connection.prepareStatement(sql, new String [] {"res_id"});
                ps.setFloat(1, reservoirEntity.getResLongitude());
                ps.setFloat(2, reservoirEntity.getResLatitude());
                ps.setString(3, reservoirEntity.getResName());
                ps.setString(4, reservoirEntity.getResScale());
                ps.setFloat(5, reservoirEntity.getResCapacity());
                ps.setString(6, reservoirEntity.getResResultType());
                ps.setString(7, reservoirEntity.getResTime());
                ps.setString(8, reservoirEntity.getIsdelete());
                java.sql.Date createDate = new java.sql.Date(reservoirEntity.getCreateTime().getTime());
                ps.setDate(9,  createDate);
                return ps;
            }
        }, keyHolder);

        int i = keyHolder.getKey().intValue();
        reservoirEntity.setResId(i);
        return reservoirEntity;

    }

    @Override
    public ReservoirEntity findReservoirById(String dataReservoirId) {
        String sql="";
        sql="select u.* from data_reservoir u where u.res_id='"+dataReservoirId+"'";
        ReservoirEntity reservoirEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ReservoirEntity.class));
        return reservoirEntity;
    }

    @Override
    public Page<ReservoirEntity> getAllReservoirByParams(ReservoirQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  u.* from data_reservoir u ");

      //  StringBuilder orderSql = new StringBuilder(" order by u.sort");

        StringBuilder whereSql = new StringBuilder(" where u.isdelete = '" + YNEnum.Y.toString() + "'");

       if (!PlatformObjectUtils.isEmpty(queryParams.getResLongitude())) {
            whereSql.append(" and u.res_longitude like %" + queryParams.getResLongitude() + "%");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getResLatitude())) {
            whereSql.append(" and u.res_latitude like %" + queryParams.getResLatitude() + "%");
        }

        String wholeSql = sql.append(whereSql).toString();
        List<ReservoirEntity> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(ReservoirEntity.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());
        Page<ReservoirEntity> reservoirEntities = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());
        return reservoirEntities;

    }

    @Override
    public void deleteReservoir(List<Integer> ids) {
        String sql = "update data_reservoir set isdelete = '" + YNEnum.N + "'  where res_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<ReservoirEntity> exportExcel() {
        return jdbcTemplate.query("select u.* from data_reservoir u where u.isdelete='"+YNEnum.Y.toString()+"'", new BeanPropertyRowMapper<>(ReservoirEntity.class));
    }

    @Override
    public void importExcel(List<ReservoirEntity> reservoirEntities) {
        jdbcTemplate.batchUpdate("insert into data_reservoir (res_longitude,res_latitude,res_name,res_scale,res_capacity,res_result_type,res_time,isdelete,create_time)values(?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {

                ps.setFloat(1,reservoirEntities.get(i).getResLongitude());
                ps.setFloat(2,reservoirEntities.get(i).getResLatitude());
                ps.setString(3,reservoirEntities.get(i).getResName());
                ps.setString(4,reservoirEntities.get(i).getResScale());
                ps.setFloat(5,reservoirEntities.get(i).getResCapacity());
                ps.setString(6,reservoirEntities.get(i).getResResultType());
                ps.setString(7,reservoirEntities.get(i).getResTime());
                ps.setString(8,reservoirEntities.get(i).getIsdelete());
                java.sql.Date sqlDate = new java.sql.Date(reservoirEntities.get(i).getCreateTime().getTime());
                ps.setDate(9,  sqlDate);
            }
            @Override
            public int getBatchSize() {
                return reservoirEntities.size();
            }
        });
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_reservoir where county = '" + county + "' and city = '" + city + "' and province = '" + province + "'   or (province ='"+province+"' and city ='' and county ='') and isdelete ='"+YNEnum.Y+"'", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_reservoir where city = '" + city + "' and province = '" + province + "'  or (province ='"+province+"' and city ='' and county ='')  and isdelete ='"+YNEnum.Y+"'  ", Integer.class);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_reservoir where province = '" + province + "'  and isdelete ='"+YNEnum.Y+"'  ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(t1.*) from data_reservoir t1 LEFT JOIN qh_map_files t2 on t1.fileuuid = t2.uuid where t1.province = ? and (t2.update_time LIKE ? or t2.create_time LIKE ?) and t2.is_delete = '0'", Integer.class, provinceName, "%" + taskYear + "%", "%" + taskYear + "%");
    }


}
